Release 10.1A: OpenEdge Data Management:
SQL Reference

UPDATE STATISTICS

Queries data tables and updates the following statistics:

Table cardinality.
Index statistics.
Column data distribution for columns that are index components.
Column data distribution for columns that are not index components.

Syntax

UPDATE ( [ TABLE | INDEX | [ ALL ] COLUMN ] STATISTICS [ AND ]) ... [ FOR table_name ] ;

Examples

The following example shows default commands for table cardinality and data distribution for index component columns:

UPDATE STATISTICS FOR Customer;

The following example shows commands for table cardinality only:

UPDATE TABLE STATISTICS FOR Customer;

The following example shows commands for new index statistics:

UPDATE INDEX STATISTICS FOR Customer;

The following example shows commands for updating column statistics for index columns only:

UPDATE COLUMN STATISTICS FOR Customer;

The following example shows commands for updating statistics for all columns:

UPDATE ALL COLUMN STATISTICS FOR Customer;

The following example shows commands to obtain table cardinality and new index statistics and column statistics for all columns:

UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR Customer;

Notes

All statistics are obtained online. Obtaining statistics does not require an exclusive lock on the schema or any table locks. Rows written to statistics tables will be exclusively locked, as in every transaction performing updates. Therefore, statistics can be obtained while normal database operations continue.
Specifying TABLE STATISTICS obtains table cardinality only. Table cardinalities are stored in the SYSTABLSTAT system catalog table.
Specifying INDEX STATISTICS obtains statistics on the number of unique values in each index. Index statistics are stored in the SYSIDXSTAT system catalog table.
Specifying COLUMN STATISTICS (without ALL) obtains statistics on the data distribution of values for each column that is an index key component.
Specifying ALL COLUMN STATISTICS obtains statistics on the data distribution of values for all columns.
The STATISTICS phrase can be repeated so that up to three statistics can be requested by a single UPDATE STATISTICS statement.
By default, for the simple statement UPDATE STATISTICS, where the type of statistics is not specified, SQL will obtain table and index column statistics. This is equivalent to the statement UPDATE TABLE STATISTICS AND COLUMN STATISTICS.
A table containing LONG data types can get table, index, and/or column statistics. The columns that are LONG data types cannot get statistics.
Obtaining table statistics runs in time proportional to the table’s primary index.
Obtaining column statistics runs in time proportional to the table’s primary index, plus an additional amount proportional to the number of columns in the table.
Obtaining index statistics runs in time proportional to the total size for all indexes for the table.
Table statistics are often the most useful statistic, as they influence join order substantially.
Index statistics are important when a table has five or more indexes. This is especially true if some of the indexes are similar to one another.
Column statistics are the most useful when applications use range predicates, such as BETWEEN and the operators <, <=, > and >=.

Authorization

Must have DBA privilege, SELECT privilege, or ownership of table.

Related statements

None